package com.sunda.spmswms.mapper;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.sunda.spmswms.entity.WhsDump;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 转储任务表 Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2021-06-29
 */
@Mapper
public interface WhsDumpMapper extends BaseMapper<WhsDump> {

    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\", RE.*\n" +
            "FROM (\n" +
            "         SELECT WD.WHS_DUMP_UUID AS \"whsDumpUuid\",\n" +
            "                WD.WHS_DUMP_NO AS \"whsDumpNo\",\n" +
            "                WD.GOODS_TYPE AS \"goodsType\",\n" +
            "                WD.BUSINESS_TYPE AS \"businessType\",\n" +
            "                WD.WERKS_DUMP_OUT AS \"werksDumpOut\",\n" +
            "                WD.WHS_LOCATION_CODE_DUMP_OUT AS \"whsLocationCodeDumpOut\",\n" +
            "                WD.DUMP_OUT_REMARK AS \"dumpOutRemark\",\n" +
            "                WD.CREATE_USERID AS \"createUserId\",\n" +
            "                WD.CREATE_DATE AS \"createDate\",\n" +
            "                WD.DUMP_OUT_OPERATOR AS \"dumpOutOperator\",\n" +
            "                WD.DUMP_OUT_DATE AS \"dumpOutDate\",\n" +
            "                WD.SPMS_STATUS AS \"spmsStatus\",\n" +
            "                WD.DUMP_OUT_SAP_DATE AS \"dumpOutSapDate\",\n" +
            "                WD.SAP_DUMP_OUT_NO AS \"sapDumpOutNo\",\n" +
            "                WD.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +
            "                WD.SAP_WHS_OUT_NO AS \"sapWhsOutNo\",\n" +
            "                WD.WERKS_DUMP_IN AS \"werksDumpIn\",\n" +
            "                WD.WHS_LOCATION_CODE_DUMP_IN AS \"whsLocationCodeDumpIn\",\n" +
            "                WD.DUMP_IN_REMARK AS \"dumpInRemark\",\n" +
            "                WD.DUMP_IN_OPERATOR AS \"dumpInOperator\",\n" +
            "                WD.DUMP_IN_DATE AS \"dumpInDate\",\n" +
            "                WD.SAP_WHS_IN_NO AS \"sapWhsInNo\",\n" +
            "                WD.SUPPLIER_CODE AS \"supplierCode\",\n" +
            "                WD.VOUCHER_TYPE AS \"voucherType\",\n" +
            "                WD.PURCHASE_GROUP AS \"purchaseGroup\",\n" +
            "                WD.PURCHASE_ORG AS \"purchaseOrg\",\n" +
            "                WD.RETURN_MARK AS \"returnMark\",\n" +
            "                WD.REMARK AS \"remark\",\n" +
            "                WD.DUMP_IN_SAP_DATE AS \"dumpInSapDate\"\n" +
            "         FROM WHS_DUMP WD\n" +
            "                  LEFT JOIN SPMS_USER U ON WD.CREATE_USERID = U.WORK_CODE " +
            "         WHERE WD.SPMS_STATUS != 0 " +
            "         <when test='whsDumpNo != null'> AND WD.WHS_DUMP_NO = #{whsDumpNo}</when> " +
            "         <when test='goodsType != null'> AND WD.GOODS_TYPE = #{goodsType}</when> " +
            "         <when test='werksDumpOut != null'> AND WD.WERKS_DUMP_OUT = #{werksDumpOut}</when> " +
            "         <when test='whsLocationCodeDumpOut != null'> AND WD.WHS_LOCATION_CODE_DUMP_OUT = #{whsLocationCodeDumpOut}</when> " +
            "         <when test='spmsStatus != null'> AND WD.SPMS_STATUS = #{spmsStatus}</when> " +
            "         <when test='sapDumpOutNo != null'> AND WD.SAP_DUMP_OUT_NO = #{sapDumpOutNo}</when> " +
            "         <when test='sapDeliveryNote != null'> AND WD.SAP_DELIVERY_NOTE = #{sapDeliveryNote}</when> " +
            "         <when test='werksDumpIn != null'> AND WD.WERKS_DUMP_IN = #{werksDumpIn}</when> " +
            "         <when test='whsLocationCodeDumpIn != null'> AND WD.WHS_LOCATION_CODE_DUMP_IN = #{whsLocationCodeDumpIn}</when> " +
            "         <when test='beginDate != null'> AND WD.CREATE_DATE &gt;= #{beginDate}</when> " +
            "         <when test='endDate != null'> AND WD.CREATE_DATE &lt;= #{endDate}</when> " +
            "  ORDER BY WD.CREATE_DATE DESC, WD.SPMS_STATUS ASC " +
            "         ) RE ORDER BY ROWNUM ASC" +
            "</script>")
    List<Map<String, Object>> pageWhsDumpDataMap(Page<Map<String, Object>> pages,
                                            @Param("whsDumpNo") String whsDumpNo,
                                            @Param("goodsType") String goodsType,
                                            @Param("werksDumpOut") String werksDumpOut,
                                            @Param("whsLocationCodeDumpOut") String whsLocationCodeDumpOut,
                                            @Param("spmsStatus") String spmsStatus,
                                            @Param("sapDumpOutNo") String sapDumpOutNo,
                                            @Param("sapDeliveryNote") String sapDeliveryNote,
                                            @Param("werksDumpIn") String werksDumpIn,
                                            @Param("whsLocationCodeDumpIn") String whsLocationCodeDumpIn,
                                             @Param("beginDate") String beginDate,
                                             @Param("endDate") String endDate);

    /** 空运人带任务提交SAP，转储表头数据获取 */
    @Select("SELECT REPLACE(WD.CREATE_DATE, '-', '') AS \"aedat\",\n" +
            "       WD.WERKS_DUMP_OUT AS \"werks\",\n" +
            "       WD.PURCHASE_GROUP AS \"ekgrp\",\n" +
            "       WD.VOUCHER_TYPE AS \"bsart\",\n" +
            "       '' AS \"zterm\",\n" +
            "       'CN01' AS \"bukrs\",\n" +
            "       WD.PURCHASE_ORG AS \"ekorg\",\n" +
            "       WD.SAP_DUMP_OUT_NO AS \"ebeln\",\n" +
            "       '空运人带单：' || WD.WHS_DUMP_NO AS \"zstring\"\n" +
            "FROM WHS_DUMP WD WHERE WHS_DUMP_UUID = #{whsDumpUuid} ")
    Map<String, Object> dataToDapHead (@Param("whsDumpUuid") String whsDumpUuid);

    /** 空运人带任务提交SAP，转储箱码信息获取 */
    @Select("SELECT WDD.BOX_NOTE AS \"packageCode\", '' AS \"flgYfp\"\n" +
            "FROM WHS_DUMP_DTL WDD WHERE WDD.WHS_DUMP_UUID = #{whsDumpUuid} \n" +
            "                        AND WDD.ACTUAL_OUT_QTY > 0 AND BOX_NOTE IS NOT NULL ")
    List<Map<String, Object>> dataToDapBoxNote (@Param("whsDumpUuid") String whsDumpUuid);

    /**
     * 空运人带任务提交SAP，转储物料信息获取
     * "       REPLACE(WD.DELIVERY_DATE, '-', '') AS \"eindt\",\n" +
     * ————20211019 交货日期字段使用 TO_CHAR(SYSDATE + 50, 'yyyyMMdd') 替换
     * */
    @Select("SELECT TO_CHAR(ROWNUM * 10) AS \"ebelp\",\n" +
            "       TO_CHAR(SYSDATE + 7, 'yyyyMMdd') AS \"eindt\",\n" +
            "       WD.WERKS_DUMP_OUT AS \"werks\",\n" +
            "       WD.WHS_LOCATION_CODE_DUMP_OUT AS \"lgort\",\n" +
            "       BND.MATERIAL_NO AS \"matnr\",\n" +
            "       TO_CHAR(BND.DOMESTIC_QTY_RECEIVED, 'fm9999999990.000') AS \"menge\",\n" +
            "       SAP.BASIC_UNIT AS \"meins\",\n" +
            "       '' AS \"loekz\",\n" +
            "       WDD.OVER_DELIVERY_LIMIT AS \"uebto\",\n" +
            "       'X' AS \"webre\",\n" +
            "       'ZNLC' AS \"lfart\",\n" +
            "       '01' AS \"vsbed\",\n" +
            "       '0001' AS \"ladgr\",\n" +
            "       '0001' AS \"tragr\",\n" +
            "       'CN01' AS \"vkorg\",\n" +
            "       '10' AS \"vtweg\",\n" +
            "       '00' AS \"spart\",\n" +
            "       WD.WERKS_DUMP_IN AS \"kunnr\"\n" +
            "FROM WHS_DUMP_DTL WDD\n" +
            "    LEFT JOIN WHS_DUMP WD ON WDD.WHS_DUMP_UUID = WD.WHS_DUMP_UUID\n" +
            "    LEFT JOIN SAP_BOX_NOTE BN ON WDD.BOX_NOTE = BN.BOX_NOTE\n" +
            "    LEFT JOIN SAP_BOX_NOTE_DTL BND ON BN.BOX_NOTE = BND.BOX_NOTE\n" +
            "    LEFT JOIN MATERIAL_SAP SAP ON BND.MATERIAL_NO = SAP.MATERIAL_NO\n" +
            "WHERE WDD.WHS_DUMP_UUID = #{whsDumpUuid} AND WDD.ACTUAL_OUT_QTY > 0 AND BND.DOMESTIC_QTY_RECEIVED > 0 AND WDD.BOX_NOTE IS NOT NULL \n")
    List<Map<String, Object>> dataToDapMaterial (@Param("whsDumpUuid") String whsDumpUuid);

}
